<?php

namespace app\api\service\intelligentlive;

use app\api\model\Account;
use app\api\model\intelligentlive\Orders;
use app\lib\exception\intelligentlive\IntelligentliveException;
use think\Db;
use think\Exception;

include "../vendor/PHPExcel/Classes/PHPExcel.php";
require_once '../vendor/PHPExcel/Classes/PHPExcel/IOFactory.php';
require_once '../vendor/PHPExcel/Classes/PHPExcel/Reader/Excel5.php';
use PHPExcel;
use PHPExcel_IOFactory;

class OrdersService
{
    private static $instance;

    public static function getInstance(): OrdersService
    {
        if (!self::$instance instanceof self) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * 订单更新、入库（更新直播间订单）
     */
    public function upOrder($params)
    {
//        Db::startTrans();
//        try {

            //数据处理
            foreach ($params as &$value) {
                $event = Orders::where("order_id",$value["order_id"])->findOrEmpty();

//                $value["order_status"] = $this->transStatus($value["order_status"]);
//                $value["total_pay_amount"] = round($value["total_pay_amount"], 2);
//                $value["commission_rate"] = round(str_replace("%", "", $value["commission_rate"]), 2);
//                $value["total_commission"] = round($value["total_commission"], 2);
//                $value["platform_service_fee"] = round($value["platform_service_fee"], 2);
//                $value["estimated_user_comission"] = round($value["estimated_user_comission"], 2);
//                $value["estimated_inst_comission"] = round($value["estimated_inst_comission"], 2)??NULL;
//                $value["media_type_group"] = $this->transType($value["media_type_group"]);
                $info = Account::where("name", $value["user_name"])->where("platform_id", 7)->cache(300)->findOrEmpty();
                if (!$info->isEmpty()) {
                    $value["buyin_id"] = $info["app_id"];
                }

                if ($event->isEmpty()){
                    Orders::create($value);
                    // 佣金率<10 的订单
                    if (floatval($value["commission_rate"]) <= 10 ) {
                        $feishu = [
                            "text"=>"新增低佣金订单通知 : "."\n"
                                ."直播间 : ".$value["user_name"]."\n"
                                ."佣金率 : ".$value["commission_rate"]."%\n"
                                ."订单id : ".$value["order_id"]."\n"
                                ."下单时间 : ".$value['pay_time']
                        ];
                        $this->feishu($value["user_name"], $feishu);
                    }
                }else{
                    Orders::where("order_id",$value["order_id"])->update($value);
                }
            }


//            DB::commit();
//
//        }catch (Exception $e){
//            // 回滚事务
//            Db::rollback();
//            throw new IntelligentliveException([
//                'msg' => '导入失败',
//                'error_code' => 20002
//            ]);
//        }
        return true;
    }

    /**
     * 订单明细 查询条件下订单的佣金统计
     */
    public function getOrder($params)
    {
        $pageSize = $params['pageSize']??100;
        $query = Orders::field("*");
        $sql = Orders::field("buyin_id,user_name,SUM(total_pay_amount) as gmv, SUM(total_commission) as money");
        // buyin_id 百应id
        if (!empty($params["buyin_id"])) {
            $query->where("buyin_id", $params["buyin_id"]);
            $sql->where("buyin_id", $params["buyin_id"]);
        }
        // order_id 订单id
        if (!empty($params["order_id"])) {
            $query->where("order_id", $params["order_id"]);
            $sql->where("order_id", $params["order_id"]);
        }
        //user_name 账号名
        if (!empty($params["user_name"])) {
            $query->where('user_name',"like","%".$params['user_name']."%");
            $sql->where('user_name',"like","%".$params['user_name']."%");
        }
        // order_status 订单状态 1：已付款2：已收货3：已结算4：退货退款
        if (!empty($params["order_status"])) {
            $query->where("order_status", $params["order_status"]);
            $sql->where("order_status", $params["order_status"]);
        }
        // pay_time 付款时间
        if (!empty($params['start_time']) && !empty($params['end_time'])) {
            $query->whereBetweenTime("pay_time",$params['start_time'],$params['end_time']);
            $sql->whereBetweenTime("pay_time",$params['start_time'],$params['end_time']);
        }
        // director 组长
        if (!empty($params["director"])) {
            $query->withjoin(['orderAccount'=> function($q)use($params){
                return $q->withField("app_id,director")->where('director',$params["director"]);
            }], 'LEFT');
            $sql->withjoin(['orderAccount'=> function($q)use($params){
                return $q->withField("app_id,director")->where('director',$params["director"]);
            }], 'LEFT');
        } else {
            $query->with('orderAccount');
            $sql->with('orderAccount');
        }
        $order = $query->order('pay_time desc')->paginate($pageSize);
        if ($order->isEmpty()) {
            return '无数据';
        }

        //统计
        $order_all = $sql->select();
        $order_refund = $sql->where("order_status", 4)->select();
        $money = [
            "total" => [
                "gmv" => $order_all[0]["gmv"],
                "commission" => $order_all[0]["money"]
            ],
            "youxiao" => [
                "gmv" => round($order_all[0]["gmv"] - $order_refund[0]["gmv"], 2),
                "commission" => round($order_all[0]["money"] - $order_refund[0]["money"], 2)
            ],
            "refund" => [
                "gmv" => $order_refund[0]["gmv"],
                "commission" => $order_refund[0]["money"]
            ]
        ];
        $data = [
            "order" => $order,
            "money" => $money,
        ];
        return $data;
    }

    /**
     * 时间段内，每个直播间的佣金
     * @param('start_time','开始时间','require')
     * @param('end_time','结束时间','require')
     */
    public function dailyCount($params)
    {
        $pageSize = $params['pageSize']??200;
        $order_all = Orders::field("buyin_id,user_name,SUM(total_pay_amount) as gmv, SUM(total_commission) as money")
            ->whereBetweenTime("pay_time",$params["start_time"],$params["end_time"])
            ->group("buyin_id")
            ->paginate($pageSize)
            ->toArray();
        $order_refund = Orders::field("buyin_id,user_name,SUM(total_pay_amount) as gmv, SUM(total_commission) as money")
            ->where("order_status", 4)
            ->whereBetweenTime("pay_time",$params["start_time"],$params["end_time"])
            ->group("buyin_id")
            ->select()
            ->toArray();
        $list = [];
        foreach ($order_all["data"] as $all) {
            $item["buyin_id"] = $all["buyin_id"];
            $item["user_name"] = $all["user_name"];
            $item["gmv"] = $all["gmv"];
            $item["yxje"] = $all["gmv"];
            $item["money"] = $all["money"];
            foreach ($order_refund as $refund) {
                if ($all["buyin_id"] == $refund["buyin_id"]) {
                    $item["yxje"] = round($all["gmv"] - $refund["gmv"], 2);
                    $item["money"] = round($all["money"] - $refund["money"], 2);
                }
            }
            $list[] = $item;
        }

        //统计
        $count_all = Orders::field("SUM(total_pay_amount) as gmv, SUM(total_commission) as money")
            ->whereBetweenTime("pay_time",$params["start_time"],$params["end_time"])
            ->select()
            ->toArray();
        $count_refund = Orders::field("SUM(total_pay_amount) as gmv, SUM(total_commission) as money")
            ->where("order_status", 4)
            ->whereBetweenTime("pay_time",$params["start_time"],$params["end_time"])
            ->select()
            ->toArray();
        $money = [
            "gmv" => round($count_all[0]["gmv"] - $count_refund[0]["gmv"], 2),
            "commission" => round($count_all[0]["money"] - $count_refund[0]["money"], 2)
        ];
        $data = [
            "list" => $list,
            "money" => $money
        ];
        return $data;
    }

    /**
     * 时间段内 每个组长的佣金统计
     * @param('start_time','开始时间','require')
     * @param('end_time','结束时间','require')
     */
    public function countByGroup($params)
    {
        if (empty($params["start_time"]) || empty($params["end_time"])) {
            return "请选择时间范围！";
        }
        $query = Orders::field("buyin_id,SUM(total_pay_amount) as gmv, SUM(total_commission) as money")
            ->withjoin(['orderAccount'=> function($q){
                return $q->withField("director");
            }], 'LEFT')
            ->whereBetweenTime("pay_time",$params["start_time"],$params["end_time"]);
        $order_all = $query->group("director")->select()->toArray();
        $order_refund = $query->where("order_status", 4)->group("director")->select()->toArray();

        $data = [];
        foreach ($order_all as $value) {
            $item = [];
            $item["director"] = $value["order_account"]["director"];
            $item["gmv"] = $value["gmv"];
            $item["r_gmv"] = 0;
            $item["yxje"] = round($value["gmv"], 2);
            $item["money"] = round($value["money"], 2);
//            $item["t_money"] = 0;
//            $item["income"] = round($item["money"], 2);
            $item["rate"] = "0%";
            foreach ($order_refund as $val) {
                if ($value["order_account"]["director"] == $val["order_account"]["director"]) {
                    $item["yxje"] = round($value["gmv"] - $val["gmv"], 2);
                    $item["r_gmv"] = $val["gmv"];
                    $item["money"] = round($value["money"] - $val["money"], 2);
//                    $item["t_money"] = round($value["t_money"] - $val["t_money"], 2);
//                    $item["income"] = round($item["money"] + $item["t_money"], 2);
                    $item["rate"] = round($val["gmv"] / $value["gmv"], 2) *100 ."%";
                }
            }
            $data[] = $item;
        }
        return $data;
    }

    // 评论通知到群
    public function pushMsg($params)
    {
        $feishu = [
            "text"=>"新评论通知 : "."\n"
                ."直播间 : ".$params["name"]."\n"
                ."内容 : ".$params['message']
        ];
        $this->feishu($feishu);
    }

    public function feishu($params){
        try {
            $url = "https://open.feishu.cn/open-apis/bot/v2/hook/621c1a42-0c4c-4793-9011-9e4e9c8e2ea4";

            $data = [
                "msg_type"=>"text",
                "content"=>[
                    "text"=>$params['text']
                ]
            ];

            $this->_post($url,$data);
        }catch (Exception $e){
//            throw new DictException(["msg" => $e->getMessage()]);
        }
    }

    public function _post($url,$data){
        $curl = curl_init();

        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
        if(!$data){
            return 'data is null';
        }
        if(is_array($data))
        {
            $data = json_encode($data);
        }
        curl_setopt($curl, CURLOPT_POST, 1);
        curl_setopt($curl, CURLOPT_POSTFIELDS, $data);
        curl_setopt($curl, CURLOPT_HEADER, 0);
        curl_setopt($curl, CURLOPT_HTTPHEADER,array(
            'Content-Type: application/json; charset=utf-8',
            'Content-Length:' . strlen($data),
            'Cache-Control: no-cache',
            'Pragma: no-cache'
        ));
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
        $res = curl_exec($curl);
        $errorno = curl_errno($curl);
        if ($errorno) {
            return $errorno;
        }
        curl_close($curl);

    }

    public function excel($params)
    {
        $query = Orders::field("*");
        // buyin_id 百应id
        if (!empty($params["buyin_id"])) {
            $query->where("buyin_id", $params["buyin_id"]);
        }
        // order_id 订单id
        if (!empty($params["order_id"])) {
            $query->where("order_id", $params["order_id"]);
        }
        //user_name 账号名
        if (!empty($params["user_name"])) {
            $query->where('user_name',"like","%".$params['user_name']."%");
        }
        // order_status 订单状态 1：已付款2：已收货3：已结算4：退货退款
        if (!empty($params["order_status"])) {
            $query->where("order_status", $params["order_status"]);
        }
        // pay_time 付款时间
        if (!empty($params['start_time']) && !empty($params['end_time'])) {
            $query->whereBetweenTime("pay_time",$params['start_time'],$params['end_time']);
        }
        // director 组长
        if (!empty($params["director"])) {
            $query->withjoin(['orderAccount'=> function($q)use($params){
                return $q->withField("app_id,director")->where('director',$params["director"]);
            }], 'LEFT');
        } else {
            $query->with('orderAccount');
        }
        $order = $query->order('pay_time desc')->select();
        if ($order->isEmpty()) {
            return '无数据';
        }

        //创建PHPExcel对象
        $objPHPExcel=new PHPExcel();

        //设置工作簿属性
        $objPHPExcel->getProperties()
            ->setCreator("Creator")
            ->setLastModifiedBy("LastModifiedBy")
            ->setTitle("Title")
            ->setSubject("Subject")
            ->setDescription("Description")
            ->setKeywords("keywords")
            ->setCategory("Category");

        //创建工作表
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');

        //添加表头
        $objPHPExcel->getActiveSheet()->setCellValue('A1','ID');
        $objPHPExcel->getActiveSheet()->setCellValue('B1','订单ID');
        $objPHPExcel->getActiveSheet()->setCellValue('C1','订单状态');
        $objPHPExcel->getActiveSheet()->setCellValue('D1','商品详情');
        $objPHPExcel->getActiveSheet()->setCellValue('E1','直播间');
        $objPHPExcel->getActiveSheet()->setCellValue('F1','计佣金额');
        $objPHPExcel->getActiveSheet()->setCellValue('G1','佣金率');
        $objPHPExcel->getActiveSheet()->setCellValue('H1','达人佣金');
//        $objPHPExcel->getActiveSheet()->setCellValue('I1','总佣金');
        $objPHPExcel->getActiveSheet()->setCellValue('J1','订单来源');
        $objPHPExcel->getActiveSheet()->setCellValue('K1','订单时间');

        $i = 2;
        foreach ($order as $row) {
            $objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$row['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B'.$i,$row['order_id']." ");
            $objPHPExcel->getActiveSheet()->setCellValue('C'.$i,$this->transStatus($row['order_status']));
            $objPHPExcel->getActiveSheet()->setCellValue('D'.$i,$row['product_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('E'.$i,$row['user_name']);
            $objPHPExcel->getActiveSheet()->setCellValue('F'.$i,$row['total_pay_amount']);
            $objPHPExcel->getActiveSheet()->setCellValue('G'.$i,$row['commission_rate']);
            $objPHPExcel->getActiveSheet()->setCellValue('H'.$i,$row['total_commission']);
//            $objPHPExcel->getActiveSheet()->setCellValue('I'.$i,$row['total_commission'] + $row['estimated_service_fee']);
            $objPHPExcel->getActiveSheet()->setCellValue('J'.$i,$this->transType($row['media_type_group']));
            $objPHPExcel->getActiveSheet()->setCellValue('K'.$i,$row['pay_time']);
            $i++;
        }

        //设置列宽度
//        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
//        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(10);

        //设置单元格格式
//        $objPHPExcel->getActiveSheet()->getStyle('B')->getNumberFormat()
//            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        //将表格导出为Excel文件
        $objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename="export.xlsx"');
        header('Cache-Control:max-age=0');
        $objWriter->save('php://output');
        exit;
    }

    public function transType($type)
    {
        $text = "";
        switch ($type){
            case 1:
                $text = "直播";
                break;
            case 2:
                $text = "其他";
                break;
            case 3:
                $text = "橱窗";
                break;
            case 4:
                $text = "视频";
                break;
            case 5:
                $text = "货架";
                break;
            case 6:
                $text = "图文";
                break;
            case "直播":
                $text = "1";
                break;
            case "其他":
                $text = "2";
                break;
            case "橱窗":
                $text = "3";
                break;
            case "视频":
                $text = "4";
                break;
            case "货架":
                $text = "5";
                break;
            case "图文":
                $text = "6";
                break;
        }
        return $text;
    }

    public function transStatus($type)
    {
        $text = "";
        switch ($type){
            case 1:
                $text = "已付款";
                break;
            case 2:
                $text = "已收货";
                break;
            case 3:
                $text = "已结算";
                break;
            case 4:
                $text = "退货退款";
                break;
            case '订单收货':
                $text = "2";
                break;
            case '订单退货退款':
                $text = "4";
                break;
            case '订单结算':
                $text = "3";
                break;
            case '订单付款':
                $text = "1";
                break;

        }
        return $text;
    }
}